package io.sdph.userprofile.dao;

import io.sdph.core.database.Transaction;
import io.sdph.core.database.exceptions.DatabaseException;
import io.sdph.documents.model.InvoiceType;
import io.sdph.products.model.Product;
import io.sdph.userprofile.model.Address;
import io.sdph.userprofile.model.User;
import io.sdph.userprofile.model.UserPermission;
import io.sdph.userprofile.model.UserType;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.sun.org.apache.bcel.internal.generic.GETSTATIC;

@Repository
public class UserManagerDB implements UserManagerDAO {

	private static final Logger log = Logger.getLogger(UserManagerDB.class);

	@Override
	public boolean deleteUser(final User user) {

		try {
			Transaction tr_delUser = new Transaction() {

				@Override
				public Object doIt() throws DatabaseException, SQLException {

					// Usuniecie z tabeli Userr
					delete("DELETE FROM Userr WHERE idUserr=?", user.getId());
					// Usuniecie z tabeli Address
					delete("DELETE FROM Addres WHERE idUserr=?", user.getId());
					// Usuniecie z tabeli UserPermissions
					delete("DELETE FROM UserrPermission WHERE idUserr=?",
							user.getId());
					return null;
				}
			};
		} catch (DatabaseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return false;
	}

	@Override
	public User getUser(final String userId) {
		User user = null;

		try {
			Transaction tr_getUser = new Transaction() {

				@Override
				public Object doIt() throws DatabaseException, SQLException {
					User user = null;

					// Pobranie rekordu z Userr
					ResultSet rs = select(
							"select * from Userr where idUserr= ?",
							userId);
					if (rs.next()) {

						user = new User(rs.getString("idUserr"));
						user.setLogin(rs.getString("loginn"));
						user.setUserName(rs.getString("userrName"));
						user.setFirstName(rs.getString("firstName"));
						user.setLastName(rs.getString("lastName"));
						user.setLicenseNumber(rs.getString("licenseNumber"));
						user.setUserType(UserType.valueOf(rs.getString("userrTYPE")));
						user.setNip(rs.getString("nip"));
						user.setRegon(rs.getString("regon"));

						// Pobranie wartosci z tabeli Addres

						Address address = new Address();

						ResultSet rs2 = select(
								"select * from Addres where idUserr= ?",
								user.getId());
						if (rs2.next()) {

							address.setCity(rs2.getString("city"));
							address.setStreet(rs2.getString("street"));
							address.setHouseNo(rs2.getString("houseNo"));
							address.setFlatNo(rs2.getString("flatNO"));
							address.setPostalCode(rs2.getString("postalCode"));

						}
						user.setAddress(address);

						// Pobranie wartosci z tabeli UserrPermission

						List<UserPermission> permissions = new ArrayList<UserPermission>();

						ResultSet rs3 = select(
								"select * from UserrPermission where idUserr= ?",
								user.getId());
						while (rs3.next()) {

							
							permissions.add(UserPermission.valueOf(rs3.getString("permission")));
							
							

						}
						user.setPermissions(permissions);

					} else {
						throw new DatabaseException();
					}

					return user;
				}
			};
			user = (User) tr_getUser.getResult();
		} catch (DatabaseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return user;
	}

	@Override
	public User logIn(final String login, final String password) {
		User user = null;
		try {
			Transaction tr = new Transaction() {
				@Override
				public Object doIt() throws DatabaseException, SQLException {
					ResultSet rs = select(
							"SELECT * FROM Userr WHERE loginn = ? AND passwordd = ?",
							login, password);
					User user = null;
					if (rs.next()) {
						user = new User(rs.getString("idUserr"));
						user.setLogin(rs.getString("loginn"));
						user.setUserName(rs.getString("userrName"));
						user.setFirstName(rs.getString("firstName"));
						user.setLastName(rs.getString("lastName"));
						user.setLicenseNumber(rs.getString("licenseNumber"));
						user.setUserType(UserType.valueOf(rs.getString("userrTYPE")));
						user.setNip(rs.getString("nip"));
						user.setRegon(rs.getString("regon"));

						// Pobranie wartosci z tabeli Addres

						Address address = new Address();

						ResultSet rs2 = select(
								"select * from Addres where idUserr= ?",
								user.getId());
						if (rs2.next()) {

							address.setCity(rs2.getString("city"));
							address.setStreet(rs2.getString("street"));
							address.setHouseNo(rs2.getString("houseNo"));
							address.setFlatNo(rs2.getString("flatNO"));
							address.setPostalCode(rs2.getString("postalCode"));

						}
						user.setAddress(address);

						// Pobranie wartosci z tabeli UserrPermission

						List<UserPermission> permissions = new ArrayList<UserPermission>();

						ResultSet rs3 = select(
								"select * from UserrPermission where idUserr= ?",
								user.getId());
						while (rs3.next()) {

							
							permissions.add(UserPermission.valueOf(rs3.getString("permission")));
							
							

						}
						user.setPermissions(permissions);
					}
					return user;
				}
			};
			user = (User) tr.getResult();
		} catch (DatabaseException e) {
			if (log.isInfoEnabled()) {
				log.info("Failed try to login [" + login + "].");
			}
		}
		return user;
	}

	@Override
	public boolean saveUser(final User user, final String password) {

		if (user.getId() != null) {
			// update

			try {
				Transaction tr_updateUser = new Transaction() {

					@Override
					public Object doIt() throws DatabaseException, SQLException {

						// Update tabeli User
						update("UPDATE Userr set loginn=?, userrName=?, firstName=?, lastName=?, licenseNumber=?, userrTYPE=?, nip=?, regon=?, passwordd=? where idUserr=?;",
								user.getLogin(), user.getUserName(), user
										.getFirstName(), user.getLastName(),
								user.getLicenseNumber(), user.getUserType()
										.getName(), user.getNip(), user
										.getRegon(), user.getId(),password);
						// Update tabeli Adres
						Address address = user.getAddress();
						update("UPDATE Addres set city=?, street=?, houseNo=?, flatNO=?, postalCode=? where idUserr=?;",
								address.getCity(), address.getStreet(),
								address.getHouseNo(), address.getFlatNo(),
								address.getPostalCode(), user.getId());
						// Usuniecie wartosci z tabeli Permissions
						delete("DELETE FROM UserrPermission WHERE idUserr=?",
								user.getId());
						// Wstawienie wartosci do tabeli Permissions
						for (int i = 0; user.getPermissions().size() > i; i++) {

							insert("INSERT INTO UserrPermission VALUES (?,?);",
									user.getId(), user.getPermissions().get(i));

						}

						return null;
					}
				};
			} catch (DatabaseException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		} else {
			// insert

			try {
				Transaction tr_insertUser = new Transaction() {

					@Override
					public Object doIt() throws DatabaseException, SQLException {

						// Wstawienie wartosci do tabeli User
						int id = insert(
								"INSERT INTO Userr (loginn,userrName,firstName,lastName,licenseNumber,userrTYPE,nip,regon,passwordd) VALUES ('?','?','?','?','?','?','?','?','?');",
								user.getLogin(), user.getUserName(), user
										.getFirstName(), user.getLastName(),
								user.getLicenseNumber(), user.getUserType()
										.getName(), user.getNip(), user
										.getRegon(),password);
						// Wstawienie wartosci do tabeli Adress
						Address address = user.getAddress();
						insert("INSERT INTO Addres (idUserr,city,street,houseNo,flatNO,postalCode) VALUES ('?','?','?','?','?','?');",
								id, address.getCity(), address.getStreet(),
								address.getHouseNo(), address.getFlatNo(),
								address.getPostalCode());
						// Wstawienie wartosci do tabeli Permissions

						for (int i = 0; user.getPermissions().size() > i; i++) {

							insert("INSERT INTO UserrPermission VALUES (?,?);",
									id, user.getPermissions().get(i));

						}

						return null;
					}
				};
			} catch (DatabaseException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}

		return false;
	}

	@Override
	public List<User> getUsers() {

		List<User> users = null;

		try {
			Transaction tr_getUsers = new Transaction() {

				@Override
				public Object doIt() throws DatabaseException, SQLException {
					List<User> users = new ArrayList<User>();

					ResultSet rs = select("select * from Userr ");
					User user = null;

					while (rs.next()) {

						user = new User(rs.getString("idUserr"));
						user.setLogin(rs.getString("loginn"));
						user.setUserName(rs.getString("userrName"));
						user.setFirstName(rs.getString("firstName"));
						user.setLastName(rs.getString("lastName"));
						user.setLicenseNumber(rs.getString("licenseNumber"));
						// Pobranie Usertype
						user.setUserType(UserType.valueOf(rs.getString("userrTYPE")));

						user.setNip(rs.getString("nip"));
						user.setRegon(rs.getString("regon"));

						// Pobranie wartosci z tabeli Addres

						Address address = new Address();

						ResultSet rs2 = select(
								"select * from Addres where idUserr= ?",
								user.getId());
						if (rs2.next()) {

							address.setCity(rs2.getString("city"));
							address.setStreet(rs2.getString("street"));
							address.setHouseNo(rs2.getString("houseNo"));
							address.setFlatNo(rs2.getString("flatNO"));
							address.setPostalCode(rs2.getString("postalCode"));

						}
						user.setAddress(address);

						// Pobranie wartosci z tabeli UserrPermission

						List<UserPermission> permissions = new ArrayList<UserPermission>();

						ResultSet rs3 = select(
								"select * from UserrPermission where idUserr= ?",
								user.getId());
						while (rs3.next()) {

							permissions.add(UserPermission.valueOf(rs3.getString("permission")));
						}
						user.setPermissions(permissions);
						users.add(user);
					}

					return users;
				}
			};
			users = (ArrayList<User>) tr_getUsers.getResult();
		} catch (DatabaseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return users;
	}

	@Override
	public void fill(User user) {
		try {
			Transaction tr_getUser = new Transaction() {

				@Override
				public Object doIt() throws DatabaseException, SQLException {
					User user = null;

					// Pobranie rekordu z Userr
					ResultSet rs = select(
							"select * from Userr where idUserr= ?",
							user.getId());
					if (rs.next()) {

						user = new User(rs.getString("idUserr"));
						user.setLogin(rs.getString("loginn"));
						user.setUserName(rs.getString("userrName"));
						user.setFirstName(rs.getString("firstName"));
						user.setLastName(rs.getString("lastName"));
						user.setLicenseNumber(rs.getString("licenseNumber"));
						// Pobranie Usertype
						user.setUserType(UserType.valueOf(rs.getString("userrTYPE")));

						user.setNip(rs.getString("nip"));
						user.setRegon(rs.getString("regon"));

						// Pobranie wartosci z tabeli Addres

						Address address = new Address();

						ResultSet rs2 = select(
								"select * from Addres where idUserr= ?",
								user.getId());
						if (rs2.next()) {

							address.setCity(rs2.getString("city"));
							address.setStreet(rs2.getString("street"));
							address.setHouseNo(rs2.getString("houseNo"));
							address.setFlatNo(rs2.getString("flatNO"));
							address.setPostalCode(rs2.getString("postalCode"));

						}
						user.setAddress(address);

						// Pobranie wartosci z tabeli UserrPermission

						List<UserPermission> permissions = new ArrayList<UserPermission>();

						ResultSet rs3 = select(
								"select * from UserrPermission where idUserr= ?",
								user.getId());
						while (rs3.next()) {

							permissions.add(UserPermission.valueOf(rs3.getString("permission")));

						}
						user.setPermissions(permissions);

					} else {
						throw new DatabaseException();
					}

					return null;
				}
			};

		} catch (DatabaseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public void modifyPassword(final User user, final String oldPassword,final String newPassword){
		
		try {
			Transaction tr = new Transaction() {
				
				@Override
				public Object doIt() throws DatabaseException, SQLException {
					
					if(update("UPDATE Userr SET passwordd=? WHERE idUserr=? AND passwordd=?",newPassword,user.getId(),oldPassword )==0){
						throw new DatabaseException();
						
					}
					
					
					
					return null;
				}
			};
		} catch (DatabaseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
}
